Writing Advanced Queries for Business Reporting

Course: MySQL Training

Topic: Writing Advanced Queries for Business Reporting

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Advanced Queries for Business Reporting

Business reporting converts raw database records into meaningful information that helps organizations monitor performance, identify trends, evaluate operations, and make informed decisions. MySQL provides advanced querying capabilities that allow analysts and developers to retrieve, combine, summarize, and interpret data from multiple tables.

Basic SQL queries retrieve individual records, while advanced reporting queries answer questions such as which department generates the most revenue, which employees have the highest sales, how monthly performance changes, and which products are performing below expectations.

Advanced business reporting commonly uses aggregate functions, joins, subqueries, Common Table Expressions (CTEs), conditional expressions, date functions, window functions, views, and query optimization techniques.

1.1 Learning Objectives

• Understand the role of advanced SQL queries in business reporting.
• Retrieve information from multiple related tables using joins.
• Summarize business data using aggregate functions and grouping.
• Filter grouped results with HAVING.
• Write subqueries and Common Table Expressions.
• Use CASE expressions to create business categories.
• Generate daily, monthly, quarterly, and yearly reports.
• Calculate rankings, running totals, and moving averages with window functions.
• Build reusable reporting views and improve query performance.
• Validate reporting results before presenting them to stakeholders.

1.2 What Is a Business Reporting Query?

A business reporting query is an SQL statement designed to answer a specific business question by extracting, calculating, filtering, or summarizing data stored in a database.

2. Understanding the Database Structure for Reporting

Business reports frequently use relational databases containing multiple tables. Understanding the table relationships is essential because incorrect joins can produce duplicate records and inaccurate totals.

2.1 Example Business Database

Consider a company database containing employees, departments, customers, orders, and order items.

2.2 Understanding Primary and Foreign Keys

A primary key uniquely identifies a record in a table. A foreign key references a key in another table and helps establish relationships between records.

departments.department_id
    |
    +---- employees.department_id

customers.customer_id
    |
    +---- orders.customer_id

orders.order_id
    |
    +---- order_items.order_id

For example, the department ID in the employees table connects each employee to a department. This relationship allows a report to display employee names alongside department names.

2.3 Creating Example Tables

The following example uses MySQL-compatible table definitions for practice. In a real database, select data types and constraints according to business requirements.

CREATE DATABASE business_reporting;
USE business_reporting;

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(12, 2),
    hire_date DATE,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    employee_id INT,
    order_date DATE NOT NULL,
    status VARCHAR(30),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
);

2.4 Inserting Sample Data

INSERT INTO departments
(department_id, department_name)
VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'IT'),
(4, 'Finance');

INSERT INTO employees
(employee_id, employee_name, department_id, salary, hire_date)
VALUES
(101, 'Aarav Sharma', 1, 55000, '2022-04-10'),
(102, 'Priya Patel', 1, 62000, '2021-08-15'),
(103, 'Rohan Mehta', 2, 48000, '2023-01-20'),
(104, 'Neha Singh', 3, 70000, '2020-11-05'),
(105, 'Ishita Rao', 4, 60000, '2022-06-12');

INSERT INTO customers
(customer_id, customer_name, city)
VALUES
(201, 'Alpha Stores', 'Mumbai'),
(202, 'Bright Retail', 'Pune'),
(203, 'City Mart', 'Mumbai'),
(204, 'Digital World', 'Nashik');

INSERT INTO orders
(order_id, customer_id, employee_id, order_date, status)
VALUES
(1001, 201, 101, '2026-01-10', 'Completed'),
(1002, 202, 102, '2026-01-15', 'Completed'),
(1003, 203, 101, '2026-02-05', 'Completed'),
(1004, 204, 103, '2026-02-18', 'Pending'),
(1005, 201, 102, '2026-03-03', 'Completed');

INSERT INTO order_items
(order_item_id, order_id, product_name, quantity, unit_price)
VALUES
(1, 1001, 'Laptop', 2, 45000),
(2, 1001, 'Mouse', 5, 800),
(3, 1002, 'Monitor', 3, 12000),
(4, 1003, 'Laptop', 1, 45000),
(5, 1003, 'Keyboard', 4, 1500),
(6, 1004, 'Monitor', 2, 12000),
(7, 1005, 'Laptop', 1, 45000);

Important: These are illustrative records. In the examples below, order revenue is calculated as quantity multiplied by unit price. For financial reporting, also define how discounts, taxes, returns, shipping charges, cancellations, and payment status affect revenue.

3. Reviewing the SELECT Statement for Reporting

The SELECT statement retrieves data from database tables. Reporting queries often include several clauses that control the selected columns, data sources, filters, grouping, and sorting.

3.1 General Query Structure

SELECT column_name, aggregate_function(column_name)
FROM table_name
JOIN another_table
    ON table_name.id = another_table.id
WHERE row_condition
GROUP BY column_name
HAVING group_condition
ORDER BY column_name DESC
LIMIT 10;

3.2 Example: Retrieve Completed Orders

SELECT
    order_id,
    customer_id,
    order_date,
    status
FROM orders
WHERE status = 'Completed'
ORDER BY order_date DESC;

This query returns completed orders and displays the newest order first. The WHERE clause filters individual rows before grouping occurs.

3.3 Choosing Relevant Columns

Business reports should include the columns required to answer the question, rather than returning every column using SELECT *. Selecting only necessary columns improves readability and may reduce data transfer.

4. Aggregate Functions for Business Analysis

Aggregate functions calculate a single result from multiple rows. They are essential for summaries such as total revenue, average salary, number of orders, and highest transaction value.

4.1 Calculate Total Order Revenue

SELECT
    SUM(oi.quantity * oi.unit_price) AS total_order_value
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed';

The expression quantity multiplied by unit_price calculates the value of each order item. SUM() adds these values together for completed orders.

4.2 Count Total Orders

SELECT
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status = 'Completed';

COUNT(*) counts qualifying order records, while COUNT(DISTINCT customer_id) counts unique customers with qualifying orders.

4.3 Calculate Average, Minimum, and Maximum Salary

SELECT
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees;

These calculations help the HR or finance team understand the salary distribution. Aggregate functions such as AVG(), MIN(), and MAX() generally ignore NULL values, so missing data should be assessed before interpreting the results.

5. GROUP BY for Department and Product Reports

GROUP BY divides rows into groups based on one or more columns. Aggregate functions then calculate a summary for each group.

5.1 Revenue by Product

SELECT
    oi.product_name,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.unit_price) AS product_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY oi.product_name
ORDER BY product_revenue DESC;

This report groups order items by product and calculates units sold and revenue. The highest-revenue products appear first.

5.2 Employee Count by Department

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
GROUP BY
    d.department_id,
    d.department_name
ORDER BY employee_count DESC;

LEFT JOIN retains departments even when no employees are assigned to them. COUNT(e.employee_id) returns zero for a department with no matching employee, unlike COUNT(*), which would count the preserved department row.

5.3 Average Salary by Department

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS average_salary,
    SUM(e.salary) AS total_salary
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
GROUP BY
    d.department_id,
    d.department_name
ORDER BY average_salary DESC;

This report combines headcount, average salary, and total salary expense in one result. If a department has no employees or all salary values are NULL, the average salary may be NULL rather than zero.

6. Filtering Groups with HAVING

WHERE filters individual records before aggregation. HAVING filters the groups produced by GROUP BY.

6.1 Products with Revenue Above a Threshold

SELECT
    oi.product_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY oi.product_name
HAVING SUM(oi.quantity * oi.unit_price) > 50000
ORDER BY total_revenue DESC;

This query returns only products whose completed-order revenue exceeds 50,000.

6.2 Departments with More Than One Employee

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
GROUP BY
    d.department_id,
    d.department_name
HAVING COUNT(e.employee_id) > 1;

6.3 WHERE Versus HAVING

7. Advanced Joins for Combined Business Reports

Joins allow a report to combine information from related tables. Choosing the correct join type and understanding each table's level of detail are critical to accurate reporting.

7.1 INNER JOIN

INNER JOIN returns rows that match the join condition in both tables.

SELECT
    o.order_id,
    c.customer_name,
    o.order_date,
    o.status
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_date;

7.2 LEFT JOIN

LEFT JOIN preserves all records from the left table and includes matching records from the right table. Missing matches appear as NULL.

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_orders DESC;

This report includes customers who have never placed an order. COUNT(o.order_id) returns zero when there is no matching order.

7.3 Join Three Tables for Sales Reporting

SELECT
    o.order_id,
    c.customer_name,
    e.employee_name AS sales_employee,
    SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
JOIN employees AS e
    ON o.employee_id = e.employee_id
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY
    o.order_id,
    c.customer_name,
    e.employee_name
ORDER BY order_value DESC;

This report connects orders to customers, employees, and order items. It calculates the total line-item value for each order.

7.4 Avoiding Duplicate Totals

Joining tables with different levels of detail can multiply rows. For example, one order may have several order items. If the order table also contains an order-level amount, summing that amount after joining to order items can count it multiple times.

To prevent inflated totals:

• Identify the grain of each table: order, order item, customer, or employee.
• Aggregate data at the appropriate level before combining independent one-to-many relationships.
• Use COUNT(DISTINCT order_id) when the business metric requires unique orders.
• Do not use DISTINCT merely to hide an incorrect join.
• Validate sample orders manually against the source data.

8. Subqueries for Advanced Reporting

A subquery is a query nested inside another SQL statement. Subqueries can compare records against summary values or identify records that satisfy a business condition.

8.1 Employees Earning Above the Company Average

SELECT
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

The inner query calculates the average salary. The outer query returns employees whose salaries exceed that value.

8.2 Products with Revenue Above Average Product Revenue

SELECT
    product_name,
    product_revenue
FROM (
    SELECT
        oi.product_name,
        SUM(oi.quantity * oi.unit_price) AS product_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY oi.product_name
) AS product_summary
WHERE product_revenue > (
    SELECT AVG(product_revenue)
    FROM (
        SELECT
            oi.product_name,
            SUM(oi.quantity * oi.unit_price) AS product_revenue
        FROM orders AS o
        JOIN order_items AS oi
            ON o.order_id = oi.order_id
        WHERE o.status = 'Completed'
        GROUP BY oi.product_name
    ) AS average_source
);

This example first calculates revenue by product, then compares each product's revenue with the average of the product-level revenue values. The derived tables use aliases, as required by MySQL.

8.3 EXISTS for Customers with Completed Orders

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'Completed'
);

EXISTS tests whether at least one matching row exists. It is useful when the report needs to identify qualifying customers without joining every order into the output.

9. Common Table Expressions (CTEs)

A Common Table Expression is a named query result introduced with WITH. CTEs can make complicated reports easier to read by separating the report into logical steps. CTEs are supported in MySQL 8.0 and later.

9.1 Basic CTE Example

WITH department_summary AS (
    SELECT
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    d.department_name,
    ds.employee_count,
    ROUND(ds.average_salary, 2) AS average_salary
FROM department_summary AS ds
JOIN departments AS d
    ON ds.department_id = d.department_id
ORDER BY ds.average_salary DESC;

The CTE calculates employee statistics once in a named query block. The outer query adds department names and sorts the results.

9.2 Multiple CTEs for a Sales Report

WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY
        o.order_id,
        o.customer_id,
        o.order_date
),
customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS completed_orders,
        SUM(order_value) AS total_spend
    FROM order_totals
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    ct.completed_orders,
    ct.total_spend
FROM customer_totals AS ct
JOIN customers AS c
    ON ct.customer_id = c.customer_id
ORDER BY ct.total_spend DESC;

The first CTE calculates each completed order's value. The second summarizes order counts and spending by customer. The final query adds customer names.

9.3 Benefits and Limitations of CTEs

• Improve readability by breaking complex logic into named stages.
• Make it easier to test intermediate calculations.
• Help organize reporting logic into meaningful steps.
• Do not automatically make a query faster; performance depends on the execution plan and data.
• Should be tested with realistic data volumes before use in production reports.

10. CASE Expressions for Business Categories

CASE creates conditional output based on specified conditions. It is useful for assigning performance categories, grouping salary levels, classifying orders, and calculating conditional metrics.

10.1 Categorize Employee Salaries

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 65000 THEN 'High Salary'
        WHEN salary >= 50000 THEN 'Medium Salary'
        ELSE 'Entry-Level Salary'
    END AS salary_category
FROM employees
ORDER BY salary DESC;

The CASE expression evaluates conditions from top to bottom and returns the result for the first matching condition.

10.2 Categorize Order Values

WITH order_totals AS (
    SELECT
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY o.order_id
)
SELECT
    order_id,
    order_value,
    CASE
        WHEN order_value >= 100000 THEN 'Large Order'
        WHEN order_value >= 50000 THEN 'Medium Order'
        ELSE 'Small Order'
    END AS order_category
FROM order_totals
ORDER BY order_value DESC;

10.3 Conditional Aggregation

Conditional aggregation allows a single query to calculate several business metrics using different conditions.

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE
        WHEN status = 'Completed' THEN 1
        ELSE 0
    END) AS completed_orders,
    SUM(CASE
        WHEN status = 'Pending' THEN 1
        ELSE 0
    END) AS pending_orders
FROM orders;

This query creates a compact order-status summary. If a status can be NULL or additional status values exist, define how those records should be handled in the report.

11. Date-Based Reporting

Business reports often compare performance over days, weeks, months, quarters, and years. MySQL provides date functions that help group and filter records according to business periods.

11.1 Revenue by Month

SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS reporting_month,
    SUM(oi.quantity * oi.unit_price) AS monthly_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY reporting_month;

DATE_FORMAT() converts each order date into a year-month label. Including the year prevents January from different years from being grouped together.

11.2 Revenue by Year

SELECT
    YEAR(o.order_date) AS reporting_year,
    SUM(oi.quantity * oi.unit_price) AS annual_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY YEAR(o.order_date)
ORDER BY reporting_year;

11.3 Filter a Date Range

SELECT
    order_id,
    customer_id,
    order_date
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2026-04-01'
ORDER BY order_date;

This query selects orders from January through March 2026. The half-open interval includes the start date and excludes the next period's start. It is also useful for DATETIME columns because it includes every time on the final intended day.

11.4 Current-Period Reports

SELECT
    COUNT(*) AS orders_this_month
FROM orders
WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND order_date < DATE_ADD(
      DATE_FORMAT(CURDATE(), '%Y-%m-01'),
      INTERVAL 1 MONTH
  );

This query counts orders from the first day of the current month up to, but not including, the first day of the next month. CURDATE() uses the database session's date context, so verify the server and session time-zone settings when reports depend on exact time boundaries.

12. Window Functions for Business Intelligence

Window functions calculate values across a related set of rows while preserving the individual rows in the result. They are useful for ranking, running totals, period comparisons, and moving averages.

Window functions are available in MySQL 8.0 and later.

12.1 Rank Employees by Salary

SELECT
    employee_id,
    employee_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
ORDER BY salary DESC;

RANK() assigns the same rank to tied salaries and leaves gaps after ties. For example, two employees ranked first can be followed by rank three.

12.2 Compare RANK(), DENSE_RANK(), and ROW_NUMBER()

12.3 Rank Products by Revenue

WITH product_sales AS (
    SELECT
        oi.product_name,
        SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY oi.product_name
)
SELECT
    product_name,
    total_revenue,
    DENSE_RANK() OVER (
        ORDER BY total_revenue DESC
    ) AS revenue_rank
FROM product_sales
ORDER BY revenue_rank, product_name;

12.4 Running Revenue Total

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(o.order_date, '%Y-%m') AS reporting_month,
        SUM(oi.quantity * oi.unit_price) AS monthly_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT
    reporting_month,
    monthly_revenue,
    SUM(monthly_revenue) OVER (
        ORDER BY reporting_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM monthly_sales
ORDER BY reporting_month;

The running total adds each month's revenue to the revenue from previous months. Only months present in the source data appear. To show months with no sales, join the report to a calendar table or generated date series.

12.5 Compare Revenue with the Previous Month

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(o.order_date, '%Y-%m') AS reporting_month,
        SUM(oi.quantity * oi.unit_price) AS monthly_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
),
monthly_comparison AS (
    SELECT
        reporting_month,
        monthly_revenue,
        LAG(monthly_revenue) OVER (
            ORDER BY reporting_month
        ) AS previous_revenue
    FROM monthly_sales
)
SELECT
    reporting_month,
    monthly_revenue,
    previous_revenue,
    monthly_revenue - previous_revenue AS revenue_change
FROM monthly_comparison
ORDER BY reporting_month;

LAG() retrieves a value from a preceding row. When months with no sales are missing, the previous row may not represent the immediately preceding calendar month. A calendar table is needed when strict month-to-month comparison is required.

13. Calculating Business KPIs

Key Performance Indicators (KPIs) are measurable values used to evaluate business performance. SQL queries can calculate KPIs directly from operational data.

13.1 Calculate Total Revenue and Average Order Value

WITH order_totals AS (
    SELECT
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY o.order_id
)
SELECT
    COUNT(*) AS completed_orders,
    SUM(order_value) AS total_revenue,
    ROUND(AVG(order_value), 2) AS average_order_value
FROM order_totals;

Calculating order-level values before calculating the average ensures that each order contributes once to the average order value, regardless of how many line items it contains.

13.2 Calculate Revenue by Sales Employee

SELECT
    e.employee_id,
    e.employee_name,
    COUNT(DISTINCT o.order_id) AS completed_orders,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM employees AS e
JOIN orders AS o
    ON e.employee_id = o.employee_id
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY e.employee_id, e.employee_name
ORDER BY total_revenue DESC;

This report attributes completed-order revenue to the employee recorded on each order. If orders can be shared among multiple salespeople, the business must define an allocation rule before using this report for commissions or employee performance.

13.3 Calculate Revenue Contribution Percentage

WITH product_sales AS (
    SELECT
        oi.product_name,
        SUM(oi.quantity * oi.unit_price) AS product_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
    GROUP BY oi.product_name
)
SELECT
    product_name,
    product_revenue,
    ROUND(
        100.0 * product_revenue /
        NULLIF(SUM(product_revenue) OVER (), 0),
        2
    ) AS revenue_percentage
FROM product_sales
ORDER BY product_revenue DESC;

The window SUM() calculates total revenue across the product rows. NULLIF() prevents division by zero. The percentage represents each product's contribution to the total revenue included in the report.

14. UNION and UNION ALL for Combining Reports

UNION combines result sets from multiple SELECT statements. Each SELECT must return the same number of columns with compatible data types.

14.1 Combine Customer Lists

SELECT customer_name AS name, city
FROM customers
WHERE city = 'Mumbai'

UNION

SELECT customer_name AS name, city
FROM customers
WHERE city = 'Pune';

UNION removes duplicate rows. UNION ALL retains duplicate rows and can be faster when duplicate elimination is unnecessary.

14.2 Compare UNION and UNION ALL

15. Creating Views for Reusable Reports

A view is a named SQL query that can be used like a table in subsequent queries. Views help standardize reporting logic and reduce the need to rewrite the same joins and calculations.

15.1 Create a Completed-Order Revenue View

CREATE VIEW completed_order_revenue AS
SELECT
    o.order_id,
    o.customer_id,
    o.employee_id,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY
    o.order_id,
    o.customer_id,
    o.employee_id,
    o.order_date;

15.2 Query the View

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS reporting_month,
    COUNT(*) AS completed_orders,
    SUM(order_value) AS total_revenue
FROM completed_order_revenue
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY reporting_month;

This query reuses the view to summarize completed orders by month. A normal MySQL view stores the query definition, not a permanently refreshed copy of the results. Its execution cost depends on the underlying query and database engine.

15.3 Benefits of Views

• Encourage consistent calculations across reports.
• Provide a simpler interface for report authors.
• Can help limit access to selected columns when combined with appropriate permissions.
• Make complex SQL logic easier to reuse.
• Still require performance testing and correct access-control configuration.

16. Query Optimization for Reporting

Business reports may process thousands or millions of rows. Optimizing queries helps reduce execution time and resource usage, especially for dashboards that refresh frequently.

16.1 Use Appropriate Indexes

Indexes can speed up filtering and joins when their structure matches the query patterns. Index design should consider the columns used in WHERE, JOIN, and ORDER BY clauses, as well as the selectivity of those columns.

CREATE INDEX idx_orders_status_date
ON orders(status, order_date);

CREATE INDEX idx_orders_customer
ON orders(customer_id);

CREATE INDEX idx_order_items_order
ON order_items(order_id);

These are example indexes, not a universal recommendation. Primary keys and foreign-key constraints may already require or create relevant indexes depending on the table definition and storage engine. Review existing indexes before adding duplicates.

16.2 Avoid Unnecessary SELECT *

-- Less targeted
SELECT *
FROM orders;

-- More targeted
SELECT
    order_id,
    customer_id,
    order_date,
    status
FROM orders;

Selecting only the columns needed for a report improves clarity and may reduce disk reads, memory use, and network transfer.

16.3 Avoid Functions on Filtered Date Columns When Possible

-- Often less index-friendly
SELECT order_id
FROM orders
WHERE YEAR(order_date) = 2026;

-- Range-based alternative
SELECT order_id
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

The range-based condition can make better use of a conventional index on order_date. Always verify the actual execution plan because performance depends on indexes, table size, data distribution, and the MySQL version.

16.4 Use EXPLAIN

EXPLAIN
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;

EXPLAIN shows the optimizer's planned approach, including table access methods, possible indexes, chosen indexes, and estimated rows. In supported MySQL versions, EXPLAIN ANALYZE executes the query and reports actual execution details. Use it carefully on expensive production queries.

16.5 Additional Optimization Practices

• Filter records early when the filtering condition does not change the required report semantics.
• Use appropriate numeric and date data types.
• Keep statistics current so the optimizer can make informed choices.
• Use indexes based on measured query patterns rather than indexing every column.
• Review join conditions to ensure they use the intended keys.
• Aggregate at the required reporting level to prevent unnecessary row expansion.
• Use pagination or date filters for very large detail reports.
• Benchmark representative queries using realistic data volumes.

17. Handling NULL Values and Data Quality

NULL represents a missing or unknown value. It is not equivalent to zero, an empty string, or the text 'NULL'. In business reporting, incorrect handling of NULL values can distort totals and averages.

17.1 Identify Missing Values

SELECT
    employee_id,
    employee_name
FROM employees
WHERE salary IS NULL;

Use IS NULL or IS NOT NULL to test missing values. Comparisons such as salary = NULL do not work as expected in SQL.

17.2 Use COALESCE Carefully

SELECT
    employee_name,
    COALESCE(salary, 0) AS displayed_salary
FROM employees;

COALESCE returns the first non-NULL value. Replacing missing salary with zero may be appropriate for a display requirement, but it can misrepresent salary statistics. Use this substitution only when the business definition supports it.

17.3 Validate Reporting Totals

SELECT
    COUNT(*) AS item_rows,
    SUM(quantity) AS total_units,
    SUM(quantity * unit_price) AS total_item_value,
    SUM(CASE
        WHEN quantity <= 0 THEN 1
        ELSE 0
    END) AS invalid_quantity_rows,
    SUM(CASE
        WHEN unit_price < 0 THEN 1
        ELSE 0
    END) AS negative_price_rows
FROM order_items;

This validation query checks basic quantity and price conditions. Actual business rules may allow negative values for returns, credits, or adjustments, so investigate exceptions instead of automatically deleting them.

18. Security and Safe Query Practices

Reporting queries can expose customer details, salary information, and financial performance. Accuracy must be combined with appropriate security and data-handling practices.

• Grant users access only to the tables, views, and columns needed for their role.
• Avoid exposing personal or confidential information in general-purpose reports.
• Use parameterized queries in applications instead of building SQL by concatenating user input.
• Validate filters supplied by report users.
• Do not grant administrative privileges merely to allow read-only reporting.
• Test complex queries on a development or staging environment before production use.
• Apply the organization's retention, privacy, and audit requirements to exported reports.

19. Common Errors in Advanced Reporting Queries

20. Step-by-Step Business Reporting Workflow

A reliable report begins with a clear business question and ends with a validated result that stakeholders can interpret correctly.

1. Define the objective: Identify the decision the report must support, such as tracking monthly revenue or department staffing.
2. Identify the metric: Define revenue, profit, active customer, completed order, or another measure precisely.
3. Identify the data sources: Find the tables, primary keys, foreign keys, and relevant columns.
4. Define the reporting grain: Decide whether one row represents an order, product, customer, employee, or month.
5. Write a basic SELECT: Retrieve the necessary records and inspect representative data.
6. Apply filters: Restrict the report by status, date, region, or other valid criteria.
7. Add joins: Combine related tables using the correct keys.
8. Calculate metrics: Use aggregate functions and conditional expressions.
9. Group and sort: Apply GROUP BY, HAVING, and ORDER BY as required.
10. Add advanced analysis: Use CTEs, subqueries, or window functions for rankings and comparisons.
11. Validate the results: Compare sample records and totals with source data or trusted reports.
12. Optimize the query: Review the execution plan and test useful indexes.
13. Document the report: Explain the metric definitions, filters, exclusions, and refresh schedule.

21. Practical Project: Monthly Sales Performance Report

Project Objective: Build a monthly report that summarizes completed orders, total revenue, units sold, and average order value. The report will help management understand sales performance across reporting periods.

21.1 Business Requirements

• Include completed orders only.
• Group results by calendar month.
• Calculate total revenue using quantity multiplied by unit price.
• Count unique orders rather than order-item rows.
• Calculate average order value from order-level totals.
• Display months in chronological order.
• Keep the reporting period clear and reproducible.

21.2 Build the Report

WITH order_totals AS (
    SELECT
        o.order_id,
        o.order_date,
        SUM(oi.quantity * oi.unit_price) AS order_value,
        SUM(oi.quantity) AS units_sold
    FROM orders AS o
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.status = 'Completed'
      AND o.order_date >= '2026-01-01'
      AND o.order_date < '2027-01-01'
    GROUP BY
        o.order_id,
        o.order_date
),
monthly_report AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS reporting_month,
        COUNT(*) AS completed_orders,
        SUM(units_sold) AS units_sold,
        SUM(order_value) AS total_revenue,
        AVG(order_value) AS average_order_value
    FROM order_totals
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    reporting_month,
    completed_orders,
    units_sold,
    total_revenue,
    ROUND(average_order_value, 2) AS average_order_value
FROM monthly_report
ORDER BY reporting_month;

21.3 Explanation of the Project Query

• order_totals CTE: Calculates one row per qualifying order and totals its line items.
• Date filter: Limits the report to the 2026 calendar year.
• monthly_report CTE: Groups the order-level data by month.
• COUNT(*): Counts orders because the first CTE contains one row per order.
• SUM(units_sold): Adds the units recorded in completed orders.
• SUM(order_value): Calculates total order-item value for each month.
• AVG(order_value): Calculates the average value per order rather than per item.

21.4 Project Validation Checklist

• Confirm that only orders with the intended completed status are included.
• Verify that order IDs are unique in the order_totals result.
• Manually calculate the value of at least two sample orders.
• Compare the report's total revenue with an independently calculated total.
• Confirm that the date range includes the intended start and end dates.
• Document whether discounts, taxes, shipping, returns, and refunds are included.
• Check whether months without completed orders should appear as zero or be omitted.
• Test the report with larger datasets and inspect its execution plan.

22. Practice Exercises

Complete these exercises using the example business_reporting database.

1. Write a query that displays all employees with their department names.
2. Calculate the average salary for every department.
3. Display customers who have no orders.
4. Find the number of completed orders placed by each customer.
5. Calculate total revenue for each product.
6. Display products whose total revenue exceeds 50,000.
7. Find employees whose salaries exceed the company-wide average.
8. Rank products by revenue using DENSE_RANK().
9. Calculate total completed-order revenue by month.
10. Calculate the average order value for each month.
11. Build a report showing completed, pending, and other orders by status.
12. Use a CTE to calculate order-level totals and then summarize them by customer.
13. Create a view for completed-order revenue and query the view by month.
14. Use EXPLAIN to inspect a query that filters orders by status and date.
15. Identify a potential duplicate-counting problem caused by joining order-level and item-level data.

22.1 Sample Solutions

Exercise 1: Employees and Departments

SELECT
    e.employee_id,
    e.employee_name,
    d.department_name,
    e.salary
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
ORDER BY d.department_name, e.employee_name;

Exercise 2: Average Salary by Department

SELECT
    d.department_name,
    ROUND(AVG(e.salary), 2) AS average_salary
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY average_salary DESC;

Exercise 3: Customers Without Orders

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Exercise 4: Completed Orders per Customer

SELECT
    c.customer_name,
    COUNT(o.order_id) AS completed_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
   AND o.status = 'Completed'
GROUP BY c.customer_id, c.customer_name
ORDER BY completed_orders DESC;

Exercise 5: Total Revenue by Product

SELECT
    oi.product_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Completed'
GROUP BY oi.product_name
ORDER BY total_revenue DESC;

23. Business Reporting Best Practices

• Use clear aliases such as total_revenue, average_order_value, and completed_orders.
• Define the business meaning of every metric before writing the query.
• Use consistent date ranges and time-zone assumptions across reports.
• Distinguish order counts from order-item counts and unique customers from customer transactions.
• Do not mix incompatible levels of detail in aggregate calculations.
• Use NULL handling that reflects business meaning instead of automatically converting all missing values to zero.
• Validate report totals against known source records and trusted calculations.
• Keep SQL readable by formatting clauses and using meaningful table aliases.
• Use CTEs and views to organize reusable reporting logic.
• Test query performance on realistic data and review the execution plan.
• Document filters, assumptions, exclusions, and metric definitions.
• Protect confidential information through appropriate permissions and data minimization.

24. Interview Questions and Answers

Q1. What is business reporting in MySQL?

Answer: Business reporting uses SQL queries to convert database records into summaries, metrics, and comparisons that support operational and strategic decisions.

Q2. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before aggregation. HAVING filters grouped results, commonly using aggregate expressions such as SUM(), COUNT(), or AVG().

Q3. Why is GROUP BY used?

Answer: GROUP BY combines rows with matching values into groups so aggregate functions can calculate summaries for each group.

Q4. What is a subquery?

Answer: A subquery is a SELECT statement nested within another SQL statement. It can calculate comparison values, filter records, or produce a derived result set.

Q5. What is a CTE?

Answer: A Common Table Expression is a named query block defined with WITH. It improves query organization and helps divide complex reporting logic into understandable stages.

Q6. What is the difference between UNION and UNION ALL?

Answer: UNION removes duplicate result rows, while UNION ALL retains them. UNION ALL is often preferable when duplicates are valid and do not need to be removed.

Q7. What are window functions?

Answer: Window functions calculate values across related rows while retaining individual result rows. Examples include RANK(), ROW_NUMBER(), LAG(), and SUM() OVER().

Q8. What is the difference between RANK() and DENSE_RANK()?

Answer: Both assign equal ranks to tied values. RANK() leaves gaps after ties, while DENSE_RANK() assigns consecutive ranks without gaps.

Q9. What is a view in MySQL?

Answer: A view is a named SQL query that can be queried like a table. It can standardize calculations and simplify access to commonly used reporting logic.

Q10. Why can a report show incorrect revenue after joining tables?

Answer: A one-to-many join can duplicate values from the table on the one side. The solution is to understand each table's grain and aggregate at the correct level before combining data.

Q11. How do you calculate average order value?

Answer: First calculate the total value of each qualifying order, then average those order totals. Averaging individual line-item values would calculate a different metric.

Q12. How can a query's performance be investigated?

Answer: Use EXPLAIN to inspect the execution plan, review table scans and index choices, verify join conditions, and test appropriate indexes with representative data.

Q13. How do you calculate month-over-month revenue change?

Answer: Aggregate revenue by month, use LAG() to retrieve the previous row's revenue, and subtract the previous value from the current value. A calendar table is needed when months without activity must be included.

Q14. Why should COUNT(DISTINCT order_id) sometimes be used?

Answer: A join to order items may produce several rows for one order. COUNT(DISTINCT order_id) counts each order once, provided that unique-order counting is the intended metric.

Q15. What should be validated before publishing a business report?

Answer: Validate filters, joins, table grain, date boundaries, metric definitions, NULL handling, duplicate counts, totals, permissions, and query performance.

25. Quick Revision Checklist

• SELECT retrieves the required data.
• WHERE filters individual records.
• JOIN combines related tables.
• GROUP BY creates reporting groups.
• SUM(), COUNT(), AVG(), MIN(), and MAX() calculate summary values.
• HAVING filters aggregate results.
• Subqueries allow nested calculations and filtering.
• CTEs organize complex queries into logical stages.
• CASE creates conditional business categories.
• Date functions support daily, monthly, quarterly, and yearly analysis.
• Window functions support ranking, running totals, and comparisons.
• Views provide reusable query definitions.
• Indexes and EXPLAIN help investigate query performance.
• Correct table grain and join logic prevent inflated totals.
• Validation ensures that business reports are accurate and meaningful.

26. Conclusion

Writing advanced queries for business reporting is an important MySQL skill for developers, database professionals, business analysts, and reporting specialists. By combining joins, aggregate functions, grouping, subqueries, CTEs, conditional expressions, date functions, and window functions, it is possible to transform operational data into useful reports.

Reliable reporting requires more than writing syntactically correct SQL. The query must use the right business definitions, preserve the intended level of detail, handle missing values appropriately, and produce totals that can be independently validated. Performance testing, access controls, and clear documentation are also essential for production reporting.

Continue learning and practicing through the JustAcademy MySQL Training Course. To learn more about course options, use the JustAcademy Course Demo Registration page or visit the JustAcademy website.